
package com.platform.common.utils;

import com.baomidou.mybatisplus.extension.service.IService;
import com.platform.common.annotation.ExcelExport;
import com.platform.common.exception.BusinessException;
import com.platform.common.utils.dataImport.BaseImportExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Function;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

/**
 * 操作excel、word、pdf、csv、excel与html互相转换
 *
 * @author
 */
@Slf4j
public class EasyPoiUtils {
    /**
     * 2003
     */
    private static final String EXCEL_XLS = "xls";
    /**
     * 2007
     */
    private static final String EXCEL_XLSX = "xlsx";

    private EasyPoiUtils() {
    }

    // 解析并导入数据
    public static void importExcel(InputStream is, String fileName, BaseImportExcel execute) {
        // 解析excel文件，获取第一个sheet
        List<String[]> list = EasyPoiUtils.readExcel(is,fileName).values().stream().findFirst().get();
        importExcel(list,execute);
    }

    // 解析并导入数据
    public static void importExcel(MultipartFile file, BaseImportExcel execute) {
        // 解析excel文件，获取第一个sheet
        List<String[]> list = EasyPoiUtils.readExcel(file).values().stream().findFirst().get();
        importExcel(list,execute);
    }

    // 解析并导入数据
    public static void importExcel(List<String[]> list, BaseImportExcel execute) {
        if (!execute.front(list)) {
            throw new BusinessException("校验未通过");
        }
        for (int i = execute.offset, num = list.size(); i < num; i++) {
            try {
                if (StringUtils.isNotBlank(String.join("", list.get(i)).trim())) {
                    execute.major(list.get(i));
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new BusinessException(String.format("在第 %s 行数据出现异常：%s", i, e.getMessage()));
            }
        }
        execute.after();
    }

    // 数据格式化
    public static <T> List<T> importExcelToMap(MultipartFile file, Function<String[], T> function) {
        // 解析excel文件，获取第一个sheet
        List<String[]> list = EasyPoiUtils.readExcel(file).values().stream().findFirst().get();
        List<T> results = new ArrayList(list.size());
        for (int i = 1, num = list.size(); i < num; i++) {
            try {
                if (StringUtils.isNotBlank(String.join("", list.get(i)).trim())) {
                    T result = function.apply(list.get(i));
                    if (result != null) {
                        results.add(result);
                    }
                }
            } catch (Exception e) {
                throw new BusinessException(String.format("解析第 %s 行数据时出现异常：%s", i, e.getMessage()));
            }
        }
        return results;
    }

    // 解析并导入数据
    public static void importExcel(MultipartFile file, Consumer<String[]> consumer) {
        // 解析excel文件，获取第一个sheet
        List<String[]> list = EasyPoiUtils.readExcel(file).values().stream().findFirst().get();
        for (int i = 1, num = list.size(); i < num; i++) {
            try {
                if (StringUtils.isNotBlank(String.join("", list.get(i)).trim())) {
                    consumer.accept(list.get(i));
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new BusinessException(String.format("解析第 %s 行数据时出现异常：%s", i, e.getMessage()));
            }
        }
    }

    // 解析并导入数据
    public static <T> void importExcel(MultipartFile file, Function<String[], T> function, IService service) {
        // 解析excel文件，获取第一个sheet
        List<String[]> list = EasyPoiUtils.readExcel(file).values().stream().findFirst().get();
        List<T> entitys = new ArrayList(list.size());
        for (int i = 1, num = list.size(); i < num; i++) {
            try {
                if (StringUtils.isNotBlank(String.join("", list.get(i)).trim())) {
                    entitys.add(function.apply(list.get(i)));
                }
            } catch (Exception e) {
                throw new BusinessException(String.format("解析第 %s 行数据时出现异常：%s", i, e.getMessage()));
            }
        }
        if (entitys.size() > 0) {
            service.saveBatch(entitys);
        }
    }

    public static Map<String, List<String[]>> readExcel(String fileName, InputStream is) {
        return readExcel(is, fileName,0);
    }

    public static Map<String, List<String[]>> readExcel(InputStream is, String fileName) {
        return readExcel(is, fileName,0);
    }

    public static Map<String, List<String[]>> readExcel(MultipartFile file) {
        return readExcel(file, null);
    }

    public static Map<String, List<String[]>> readExcel(MultipartFile file, Integer getSheetNum) {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        return readExcel(workbook,getSheetNum);
    }

    public static Map<String, List<String[]>> readExcel(InputStream is, String fileName, Integer getSheetNum) {
        //检查文件
        checkFile(is,fileName);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(is,fileName);
        return readExcel(workbook,getSheetNum);
    }

    /**
     * 读入excel文件，解析后返回 指定 sheet页
     * getSheetNum 为空则查询 多sheet页，
     *
     * @return key:sheetName value:sheet内容
     */
    public static Map<String, List<String[]>> readExcel(Workbook workbook, Integer getSheetNum) {
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        Map<String, List<String[]>> map = new HashMap<>(16);
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets() && (sheetNum == 0 || getSheetNum == null); sheetNum++) {
                List<String[]> list = new ArrayList<>();
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(getSheetNum == null ? sheetNum : getSheetNum);
                String sheetName = workbook.getSheetName(sheetNum);
                if (sheet == null) {
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getLastCellNum();  // 原来 row.getPhysicalNumberOfCells()
                    String[] cells = new String[lastCellNum];
                    if (cells.length == 0) {
                        continue;
                    }
                    //循环当前行
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
                if (list.size() > 0) {
                    map.put(sheetName, list);
                }
            }
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public static void checkFile(MultipartFile file) {
        //判断文件是否存在
        if (null == file) {
            log.error("文件不存在！");
            throw new BusinessException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(EXCEL_XLS) && !fileName.endsWith(EXCEL_XLSX)) {
            log.error(fileName + "不是excel文件");
            throw new BusinessException(fileName + "不是excel文件");
        }
    }

    private static void checkFile(InputStream is, String fileName) {
        //判断文件是否存在
        if (null == is) {
            log.error("文件不存在！");
            throw new BusinessException("文件不存在！");
        }
        //判断文件是否是excel文件
        if (!fileName.endsWith(EXCEL_XLS) && !fileName.endsWith(EXCEL_XLSX)) {
            log.error(fileName + "不是excel文件");
            throw new BusinessException(fileName + "不是excel文件");
        }
    }

    private static Workbook getWorkBook(InputStream is,String fileName) {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(EXCEL_XLS)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(EXCEL_XLSX)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.info(e.getMessage());
        }
        return workbook;
    }

    private static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(EXCEL_XLS)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(EXCEL_XLSX)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.info(e.getMessage());
        }
        return workbook;
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        System.out.println(cell.getCellType());
        if (cell.getCellType() == NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                // 获取日期类型
                try {
                    cell.setCellType(NUMERIC);
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = DateUtils.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.valueOf(cellValue)));
                    }
                } catch (Exception e) {

                }
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    cellValue = String.valueOf(cell.getCellFormula());
                }
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public static void cardSnExport(HttpServletResponse response,
                                    List<Map<String, Object>> data, Class<?> tClass) throws Exception{
//        ExcelWriter writer = ExcelUtil.getWriter();

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();

        ServletOutputStream outputStream = null;
    try {
        Field[] fields = tClass.getDeclaredFields();

        List<ExcelTitle> titles = new ArrayList<>();
        ExcelTitle title;
        for (Field field : fields) {
            // 是否引用ExcelExport注解
            boolean bool = field.isAnnotationPresent(ExcelExport.class);
            if (bool) {
                title = new ExcelTitle();
                title.setName(field.getName());
                title.setTitle(field.getAnnotation(ExcelExport.class).value());
                titles.add(title);
            }
        }

        HSSFRow tRow =sheet.createRow(0);
        for (int j = 0 ;j<titles.size();j++){ //设置表头
            HSSFCell cell = tRow.createCell(j);
            String titleCell = titles.get(j).getTitle();
            cell.setCellValue(titleCell);
        }
        for (int i = 0;i<data.size();i++){
            HSSFRow row =sheet.createRow(i+1);
            for (int j = 0 ;j<titles.size();j++){
                HSSFCell cell = row.createCell(j);
                String name = titles.get(j).getName();
                cell.setCellValue((String)data.get(i).get(name));
            }
        }
        
        Biff8EncryptionKey.setCurrentUserPassword("xtns@!dyjr");
        wb.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(),"");

        response.setCharacterEncoding("UTF-8");
        String name = URLEncoder.encode("卡券信息.xlsx", "UTF-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + name);
        outputStream = response.getOutputStream();

        wb.write(outputStream);
//        writer.flush(outputStream,true);
    } finally {
        wb.close();
//        writer.close();
        if(ObjectUtils.allNotNull(outputStream)){
            outputStream.close();
        }
    }

    }


}
